﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data.Common;

namespace DAL
{
    public class CategoryDAL : AbstractDAL<Category>
    {
        static readonly string INSERT = "insert into category values(null,@ParentID,@Name);select last_insert_rowid() newid;";
        static readonly string UPDATE = "update category set ParentID=@ParentID,Name=@Name where id=@ID";
        static readonly string DELETE = "delete from category where id=@ID";
        static readonly string SELECT = "select * from  category where id=@ID";
        static readonly string SELECT_ALL = "select * from  category";
        static readonly string SELECT_CHILDREN = "select * from  category where ParentID=@ParentID";
        static readonly string SELECT_SeachCategoryByName = "select * from  category where Name like @Name";

        protected override string InsertCommand
        {
            get
            {
                return INSERT;
            }
        }

        protected override string UpdateCommand
        {
            get { return UPDATE; }
        }

        protected override string DeleteCommand
        {
            get { return DELETE; }
        }

        protected override string SelectCommand
        {
            get { return SELECT; }
        }

        protected override string SelectAllCommand
        {
            get { return SELECT_ALL; }
        }

        protected override DbParameter[] GetInsertParameter(Category model)
        {
            return new DbParameter[] { SqlHelper.CreateParameter("@ParentID", model.ParentID), SqlHelper.CreateParameter("@Name", model.Name) };
        }

        protected override void SyncID(Category model, object scalar)
        {
            model.ID = (long)scalar;
        }

        protected override DbParameter[] GetUpdateParameter(Category model)
        {
            return new DbParameter[] { SqlHelper.CreateParameter("@ID", model.ID), SqlHelper.CreateParameter("@ParentID", model.ParentID), SqlHelper.CreateParameter("@Name", model.Name) };
        }

        protected override DbParameter[] GetDeleteParameter(Category model)
        {
            return new DbParameter[] { SqlHelper.CreateParameter("@ID", model.ID) };
        }

        protected override DbParameter[] GetSeleteParameter(long id)
        {
            return new DbParameter[] { SqlHelper.CreateParameter("@ID", id) };
        }

        protected override Category GetModel(DbDataReader reader)
        {
            Category entity = new Category();
            entity.ID = (long)reader["ID"];
            entity.ParentID = (long)reader["ParentID"];
            entity.Name = (string)reader["Name"];
            return entity;
        }

        public List<Category> GetChildren(long id)
        {
            List<Category> list = new List<Category>();
            DbParameter parameter = SqlHelper.CreateParameter("@ParentID", id);
            DbCommand command = null;
            DbDataReader reader = null;
            try
            {
                command = SqlHelper.GetCommand(SELECT_CHILDREN);
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(GetModel(reader));
                }
            }
            catch
            {
                SqlHelper.Close(command, reader);
            }
            return list;
        }

        public List<Category> SeachCategoryByName(string name, List<long> filterID = null)
        {
            List<Category> list = new List<Category>();
            DbParameter parameter = SqlHelper.CreateParameter("@Name", "%" + name + "%");
            DbCommand command = null;
            DbDataReader reader = null;
            string sql = SELECT_SeachCategoryByName;
            if (filterID != null)
            {
                sql = sql + " and id in(" + string.Join(",", filterID) + ")";
            }

            try
            {
                command = SqlHelper.GetCommand(sql);
                command.Parameters.Add(parameter);
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(GetModel(reader));
                }
            }
            catch
            {
                SqlHelper.Close(command, reader);
            }
            return list;
        }
    }
}
